/**
 * DaoQxj.java
 *2013年10月7日下午3:48:35
 *LINKT
 */
package com.jskj.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.jskj.commons.DBConn;

/**
 * @author LINKT
 *
 */
public class DaoQxj {

        public String  getQxjList(String sbyf,String swjg_dm) {
                String sql="";
                DBConn conn=new DBConn();
                String str="";
                if (swjg_dm.equals("11305000000")) {
                        sql="select c.swjg_mc,b.* from "+
                                        "(select a.swjg_dm , "+
                                                "sum(case when [yp]='102' then [ylsum] else 0 end)as yp_93, "+
                                                "sum(case when [yp]='104' then [ylsum] else 0 end)as yp_97, "+
                                                "sum(case when [yp]='202' then [ylsum] else 0 end)as yp_0, "+
                                                "sum(case when [yp]not in('102','104','202') then [ylsum] else 0 end)as yp_other, "+
                                                "sum(ylsum) as yp_sum, "+
                                                "'"+sbyf+"' as sbyf "+
                                        "from v_swjgsum a "+
                                        "where a.ny='"+sbyf+"' "+
                                        "group by a.swjg_dm) b "+
                                "join dm_swjg c on(b.swjg_dm=c.swjg_dm)";
                }else{
                        sql="select c.swjg_mc,b.* from "+
                                        "(select a.swjg_dm , "+
                                                "sum(case when [yp]='102' then [ylsum] else 0 end)as yp_93, "+
                                                "sum(case when [yp]='104' then [ylsum] else 0 end)as yp_97, "+
                                                "sum(case when [yp]='202' then [ylsum] else 0 end)as yp_0, "+
                                                "sum(case when [yp]not in('102','104','202') then [ylsum] else 0 end)as yp_other, "+
                                                "sum(ylsum) as yp_sum, "+
                                                "'"+sbyf+"' as sbyf "+
                                        "from v_swjgsum a "+
                                        "where a.ny='"+sbyf+"'  and a.swjg_dm='"+swjg_dm+"'"+
                                        "group by a.swjg_dm) b "+
                                "join dm_swjg c on(b.swjg_dm=c.swjg_dm)";
                }
                int totalcount=0;
                StringBuffer sb=new StringBuffer();
                ResultSet rs=conn.SelectSQL(sql);
                try {
                        while (rs.next()) {
                                sb.append("{\"swjg_mc\":\""+rs.getString("swjg_mc")+"\",\"swjg_dm\":\""+rs.getString("swjg_dm")+"\",\"sbyf\":\""+rs.getString("sbyf")+"\","
                                                + "\"yp_93\":\""+rs.getString("yp_93")+"\","
                                                + "\"yp_97\":\""+rs.getString("yp_97")+"\","
                                                + "\"yp_0\":\""+rs.getString("yp_0")+"\","
                                                + "\"yp_other\":\""+rs.getString("yp_other")+"\","
                                                + "\"yp_sum\":\""+rs.getString("yp_sum")+"\""
                                                                + "},");
                                totalcount++;
                        }
                        rs.close();
                        conn.Close();
                        if (sb.length()>0) {
                                sb=sb.deleteCharAt(sb.length()-1);
                        }
                        str="{\"totalCount\":"+totalcount+",\"items\":["+sb.toString()+"]}";;
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return str;
                
                
        }
}
